﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using Util;

namespace DAO
{
    /// <summary>
    /// Summary description for Category
    /// </summary>
    public class Category
    {
        DBConnection DBConnection;
        public Category()
        {
            DBConnection = new DBConnection();
            //
            // TODO: Add constructor logic here
            //
        }
        /// <summary>
        /// Get all DTO.Category instance from DB.
        /// </summary>
        /// <returns>List<> of all DTO.Category instances.</returns>
        public List<DTO.Category> GetAllCategorys()
        {
            Log.LogInfo("Begin GetAllCategorys()");
            String query = "SELECT * FROM [Category];";
            SqlDataReader reader = DBConnection.ExecuteReader(query);
            List<DTO.Category> categoryDTOs = new List<DTO.Category>();
            try
            {
                while (reader.Read())
                {
                    categoryDTOs.Add(new DTO.Category(reader));
                }
            }
            catch (Exception ex)
            {
                Log.LogError("Error GetAllCategorys(): " + ex.StackTrace);
            }

            Log.LogInfo("End GetAllCategorys()");
            return categoryDTOs;
        }
        /// <summary>
        /// Get all DTO.Category of a User from DB.
        /// </summary>
        /// <returns>List<> of all DTO.Category instances.</returns>
        public List<DTO.Category> GetCategorysOfUser(string UserID)
        {
            Log.LogInfo("Begin GetAllCategorys()");
            String query = "SELECT * FROM [Category] WHERE UserID='"+UserID+"'";
            SqlDataReader reader = DBConnection.ExecuteReader(query);
            List<DTO.Category> categoryDTOs = new List<DTO.Category>();
            try
            {
                while (reader.Read())
                {
                    categoryDTOs.Add(new DTO.Category(reader));
                }
            }
            catch (Exception ex)
            {
                Log.LogError("Error GetAllCategorys(): " + ex.StackTrace);
            }

            Log.LogInfo("End GetAllCategorys()");
            return categoryDTOs;
        }
        /// <summary>
        /// Get DTO.Category instance from DB.
        /// </summary>
        /// <param name="ID">Category ID</param>
        /// <returns>DTO.Category instance which has the specific ID.</returns>
        public DTO.Category GetCategory(String ID)
        {
            Log.LogInfo("Begin GetCategory(String ID)");
            String query = "SELECT * FROM [Category] WHERE [Category].[ID]='" + ID + "';";
            SqlDataReader reader = DBConnection.ExecuteReader(query);
            DTO.Category categoryDTO = null;
            try
            {
                if (reader.Read())
                    categoryDTO = new DTO.Category(reader);
            }
            catch (Exception ex)
            {
                Log.LogError("Error GetCategory(String ID): " + ex.Message);
            }

            Log.LogInfo("End GetCategory(String ID)");
            return categoryDTO;
        }

        /// <summary>
        /// Insert Category into database.
        /// </summary>
        /// <param name="Category">DTO.Category instance to insert into database.</param>
        public int InsertCategory(DTO.Category Category)
        {
            Log.LogInfo("Begin InsertCategory()");
            String query = "";            
            query += "INSERT INTO CATEGORY ( UserID, Name) VALUES ('"+ Category.UserID + "','" + Category.Name + "')";            
            int result = DBConnection.ExecuteNonQuery(query);
            DBConnection.Close();

            query = "";
            query += "SELECT MAX(ID) FROM CATEGORY WHERE UserID='" + Category.UserID + "'";
            DBConnection = new Util.DBConnection();
            SqlDataReader reader = DBConnection.ExecuteReader(query);
            reader.Read();
            result = reader.GetInt32(0);
            DBConnection.Close();
            Log.LogInfo("Insert " + result.ToString() + "row(s).");
            Log.LogInfo("End InsertCategory()");
            return result;
        }

        /// <summary>
        /// Update Category info into database.
        /// </summary>
        /// <param name="Category">Category instance to be updated.</param>
        public int UpdateCategory(DTO.Category Category)
        {
            Log.LogInfo("Begin UpdateCategory()");
            String query = "UPDATE CATEGORY SET UserID = '" + Category.UserID + "'," + " Name = '" + Category.Name + "' WHERE ID = '" + Category.ID + "'";
            int result = DBConnection.ExecuteNonQuery(query);
            DBConnection.Close();
            Log.LogInfo("Update " + result.ToString() + "row(s).");
            Log.LogInfo("End InsertCategory()");
            return result;
        }
        /// <summary>
        /// Delete a specific Category from DB.
        /// </summary>
        /// <param name="Category">The Category to be deleted.</param>
        public int DeleteCategory(DTO.Category Category)
        {
            Log.LogInfo("Begin DeleteCategory()");
            String query = "DELETE FROM [Category] WHERE [Category].[ID]='" + Category.ID + "';";
            int result = DBConnection.ExecuteNonQuery(query);
            Log.LogInfo("Deleted " + result.ToString() + "row(s).");
            Log.LogInfo("End DeleteCategory()");
            return result;
        }

        /// <summary>
        /// Delete a Category in database using ID.
        /// </summary>
        /// <param name="ID">Category ID.</param>
        public void DeleteCategory(String ID)
        {
            Log.LogInfo("Begin DeleteCategory()");
            String query = "DELETE FROM [Category] WHERE [Category].[ID]='" + ID + "';";
            int result = DBConnection.ExecuteNonQuery(query);
            Log.LogInfo("Deleted " + result.ToString() + "row(s).");
            Log.LogInfo("End DeleteCategory()");
        }
    }
}